/**
 * 
 */
package persistence.database;

import java.sql.*;

import core.exception.SaveException;
import persistence.DataPersistor;
import persistence.ProductDAO;
import persistence.ProductDTO;

/**
 * Product DAO for database persistence
 * @author Sabrina
 *
 */
public class DBProductDAO implements ProductDAO {

	private DBTransactionManager _transMgr;
	
	/**
	 * Constructs the Product DAO for database persistence
	 */
	public DBProductDAO()
	{
		_transMgr = (DBTransactionManager) DataPersistor.instance().getTransactionManager();
	}
	
	/**
	 * @inheritDoc
	 */
	@Override
	public void addProduct(ProductDTO dto) throws SaveException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try
		{
			String sql = "select * from Products Where Barcode = ?;";
			Connection connection = _transMgr.getConnection();
			stmt = connection.prepareStatement(sql);
			stmt.setString(1,  dto.getProductBarcode());
			rs = stmt.executeQuery();
			stmt = null;
			if (!rs.next())
			{
			
				sql = "INSERT INTO Products (Barcode, Description, SizeValue, SizeUnit, " +
						"ThreeMonth, ShelfLife) VALUES (?, ?, ?, ?, ?, ?);";
				stmt = connection.prepareStatement(sql);
				stmt.setString(1, dto.getProductBarcode());
				stmt.setString(2, dto.getDescription());
				stmt.setFloat(3, dto.getSizeValue());
				stmt.setString(4,  dto.getSizeUnit());
				stmt.setInt(5,  dto.getThreeMonthSupply());
				stmt.setInt(6,  dto.getShelfLife());
				
				if (stmt.executeUpdate() != 1)
				{
					throw new SaveException("Incorrect number of lines added");
				}		
			}
		}
		catch(Exception e)
		{
			try {
				rs.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			throw new SaveException(e.getMessage());
		}
		finally
		{
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				throw new SaveException (e.getMessage());
			}
		}

	}

	/**
	 * @inheritDoc
	 */
	@Override
	public void updateProduct(ProductDTO dto) throws SaveException {
		PreparedStatement stmt = null;

		try
		{
			String sql = "Update Products " +
					"set Description = ?, SizeValue = ?, SizeUnit = ?," +
						"ThreeMonth= ?, ShelfLife = ? " +
					"where Barcode = ?;";
			Connection connection = _transMgr.getConnection();
			stmt = connection.prepareStatement(sql);
			stmt.setString(1, dto.getDescription());
			stmt.setFloat(2, dto.getSizeValue());
			stmt.setString(3,  dto.getSizeUnit());
			stmt.setInt(4,  dto.getThreeMonthSupply());
			stmt.setInt(5,  dto.getShelfLife());
			stmt.setString(6, dto.getProductBarcode());
			
			if (stmt.executeUpdate() != 1)
			{
				throw new SaveException("Incorrect number of lines updated");
			}	
		}
		catch (Exception e)
		{
			throw new SaveException (e.getMessage());
		}
		finally
		{
			try
			{
				if (stmt != null)
					stmt.close();
			}
			catch(SQLException e)
			{
				throw new SaveException (e.getMessage());
			}
		}
	}

	/**
	 * @inheritDoc
	 */
	@Override
	public void removeProduct(ProductDTO dto) throws SaveException {
		PreparedStatement stmt = null;
		
		try
		{
			String sql = "Delete from Products where Barcode = ?;";
			Connection connection = _transMgr.getConnection();
			stmt = connection.prepareStatement(sql);
			stmt.setString(1, dto.getProductBarcode());
			
			if (stmt.executeUpdate() != 1)
			{
				throw new SaveException ("More than 1 product removed");
			}
		}
		catch (Exception e)
		{
			throw new SaveException (e.getMessage());
		}
		finally
		{
			try
			{
				if (stmt != null)
					stmt.close();
			}
			catch (SQLException e)
			{
				throw new SaveException (e.getMessage());
			}
		}

	}

}
